package com.dz.risk.common;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import com.dz.risk.exception.VerifyException;
import com.dz.risk.pojo.domain.ReportFormsDataValueEntity;
import com.dz.risk.pojo.model.FormulaModel;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.nfunk.jep.JEP;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author baohan
 * @className ExcelUtils
 * @date 2021/6/10 11:43
 */
@Slf4j
public class ExcelUtils {

    public static final String OS_NAME = "os.name";
    public static final String WINDOWS = "Windows";

    /**
     * 根据模板导出
     *
     * @param path      url
     * @param map       导出参数
     * @param excelName 导出文件名
     * @param response
     */
    public static void exportExcelTemplate(String path,
                                           Map<String, Object> map,
                                           String excelName,
                                           HttpServletResponse response) {
        Workbook workbook = ExcelExportUtil.exportExcel(new TemplateExportParams(
                convertTemplatePath(path)), map);
        // 重置响应对象
        response.reset();
        // 指定下载的文件名--设置响应头
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Cache-Control", "no-cache");
        response.setDateHeader("Expires", 0);
        response.setCharacterEncoding("utf-8");
        try {
            response.setHeader("Content-Disposition",
                    "attachment; filename=" + new String(excelName.getBytes("gbk"), "iso8859-1") + ".xlsx");
            OutputStream output = response.getOutputStream();
            BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);
            workbook.write(bufferedOutPut);
            bufferedOutPut.flush();
            bufferedOutPut.close();
            output.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    /**
     * 获取单元格 不同类型
     *
     * @param cell
     * @return
     */
    public static Object getCellValue(Cell cell) {
        if (null != cell) {
            switch (cell.getCellType()) {
                // 数字
                case NUMERIC:
                    //防止科学计数法转换
                    DecimalFormat df = new DecimalFormat("0.00");
                    return String.valueOf(df.format(cell.getNumericCellValue()));
                // 字符串
                case STRING:
                    return cell.getStringCellValue();
                // Boolean
                case BOOLEAN:
                    return cell.getBooleanCellValue();
                // 公式
                case FORMULA:
                    //// poI 读取excell单元格公式值
                    double numericCellValue = cell.getNumericCellValue();
                    //// poI 读取excell单元格公式
                    String cellFormula = cell.getCellFormula();
                    return String.valueOf(numericCellValue) + SysConstant.PASER_ZY + cellFormula;
                // 空值
                case BLANK:
                    return "";
                // 故障
                case ERROR:
                    return "";
                default:
                    break;
            }
        }
        return "";
    }


    /**
     * 获取单元格 不同类型(小数点后面保留两位)
     *
     * @param cell
     * @return
     */
    public static Object getCellValueForNumber(Cell cell) {
        if (null != cell) {
            switch (cell.getCellType()) {
                // 数字
                case NUMERIC:
                    //防止科学计数法转换
                    DecimalFormat df = new DecimalFormat("0.0000");
                    return String.valueOf(df.format(cell.getNumericCellValue()));
                // 字符串
                case STRING:
                    return cell.getStringCellValue();
                // Boolean
                case BOOLEAN:
                    return cell.getBooleanCellValue();
                // 公式
                case FORMULA:
                    //// poI 读取excell单元格公式值
                    double numericCellValue = cell.getNumericCellValue();
                    //// poI 读取excell单元格公式
                    String cellFormula = cell.getCellFormula();
                    return String.valueOf(numericCellValue) + SysConstant.PASER_ZY + cellFormula;
                // 空值
                case BLANK:
                    return "";
                // 故障
                case ERROR:
                    return "";
                default:
                    break;
            }
        }
        return "";
    }

    /**
     * JEP 计算
     *
     * @param formulaModel
     */
    public static String calculate(FormulaModel formulaModel) {
        try {
            String formula = formulaModel.getFormulaDetails();
            List<String> parseFormula = formulaModel.getParseFormula();
            String[] split1 = formula.split(SysConstant.REGEXS);
            JEP jep = new JEP();
            for (int i = 0; i < split1.length; i++) {
                jep.addVariable(verifyStrings(split1[i]), Double.valueOf(parseFormula.get(i)));
            }
            jep.parseExpression(formula);
            return String.valueOf(jep.getValue());
        } catch (NumberFormatException e) {
            log.error("计算失败！！+" + formulaModel.toString());
            e.printStackTrace();
        }
        return null;
    }


    /**
     * 验证特殊字符
     *
     * @param str
     * @return
     */
    public static String verifyStrings(String str) {
        if (str.contains("(")) {
            str = str.replace("(", "");
        }
        if (str.contains(")")) {
            str = str.replace(")", "");
        }
        return str;
    }

    /**
     * 读取导出文件地址 区分是windows 还是linux 下配置
     *
     * @param path
     * @return
     */
    public static String convertTemplatePath(String path) {
        //如果是windows 则直接返回
        if (System.getProperties().getProperty(OS_NAME).contains(WINDOWS)) {
            return path;
        }
        Resource resource = new ClassPathResource(path);
        FileOutputStream fileOutputStream = null;
        // 将模版文件写入到 tomcat临时目录
        String folder = System.getProperty("catalina.home");
        File tempFile = new File(folder + File.separator + path);
        // 文件存在时 不再写入
        if (tempFile.exists()) {
            return tempFile.getPath();
        }
        File parentFile = tempFile.getParentFile();
        // 判断父文件夹是否存在
        if (!parentFile.exists()) {
            parentFile.mkdirs();
        }
        try {
            BufferedInputStream bufferedInputStream = new BufferedInputStream(resource.getInputStream());
            fileOutputStream = new FileOutputStream(tempFile);
            byte[] buffer = new byte[10240];
            int len = 0;
            while ((len = bufferedInputStream.read(buffer)) != -1) {
                fileOutputStream.write(buffer, 0, len);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (fileOutputStream != null) {
                try {
                    fileOutputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return tempFile.getPath();
    }

    /**
     * 对象转换为Map<String, Object>的工具类
     *
     * @param obj 要转换的对象
     * @return map
     * @throws IllegalAccessException
     */
    public static Map<String, Object> objectToMap(Object obj) throws IllegalAccessException {
        Map<String, Object> map = new HashMap<>(16);
        Class<?> clazz = obj.getClass();
        for (Field field : clazz.getDeclaredFields()) {
            field.setAccessible(true);
            String fieldName = field.getName();
            /*
             * Returns the value of the field represented by this {@code Field}, on the
             * specified object. The value is automatically wrapped in an object if it
             * has a primitive type.
             * 注:返回对象该该属性的属性值，如果该属性的基本类型，那么自动转换为包装类
             */
            Object value = field.get(obj);
            map.put(fieldName, value);
        }
        return map;
    }

    /**
     * 获取具体文件
     *
     * @return 文件对象
     */
    public static Workbook getWorkbook(InputStream inputStream, String fileName) throws IOException {
        Workbook workbook;
        //03版后缀
        final String excelVersionOne = "xls";
        //07版后缀
        final String excelVersionTwo = "xlsx";
        //根据文件流获取文件后缀
        String suffix = getFileSuffixByInputStream(fileName);

        //根据Excel版本获取模板对象
        if (excelVersionOne.equals(suffix)) {
            workbook = new HSSFWorkbook(inputStream);
        } else if (excelVersionTwo.equals(suffix)) {
            workbook = new XSSFWorkbook(inputStream);
        } else {
            throw new VerifyException("Excel版本格式不正确");
        }
        return workbook;
    }

    /**
     * 根据文件流获取文件后缀
     *
     * @param fileName 文件名
     * @return 文件后缀
     */
    private static String getFileSuffixByInputStream(String fileName) {
        final String symbol = ".";

        int i = fileName.lastIndexOf(symbol);
        return fileName.substring(++i);
    }
}
